import sqlite3

# 1. 连接数据库（不存在则创建）
conn = sqlite3.connect('example.db')

# 2. 创建游标对象
cursor = conn.cursor()

# 3. 创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        major TEXT
    )
''')

# 4. 插入数据
cursor.execute("INSERT INTO students (name, age, major) VALUES (?, ?, ?)", 
              ("张三", 20, "计算机科学"))

# 批量插入
students = [
    ("李四", 21, "数学"),
    ("王五", 19, "物理学")
]
cursor.executemany("INSERT INTO students (name, age, major) VALUES (?, ?, ?)", students)

# 提交事务
conn.commit()

# 5. 查询数据
cursor.execute("SELECT * FROM students WHERE age > 19")
print("年龄大于19的学生:")
for row in cursor.fetchall():
    print(row)

# 6. 更新数据
cursor.execute("UPDATE students SET age = ? WHERE name = ?", (22, "李四"))

# 7. 删除数据
cursor.execute("DELETE FROM students WHERE name = ?", ("王五",))

# 再次查询验证
cursor.execute("SELECT * FROM students")
print("\n所有学生:")
for row in cursor.fetchall():
    print(row)

# 8. 关闭连接
cursor.close()
conn.close()
